package com.sinog.front.util;

import com.sinog.core.model.ChartConfig;
import com.sinog.core.utilbean.Constants;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.StandardChartTheme;
import org.jfree.chart.axis.ValueAxis;
import org.jfree.chart.labels.StandardCategoryItemLabelGenerator;
import org.jfree.chart.labels.StandardPieSectionLabelGenerator;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.PiePlot;
import org.jfree.chart.plot.PiePlot3D;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.renderer.category.BarRenderer;
import org.jfree.chart.renderer.category.StandardBarPainter;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.general.DefaultPieDataset;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import sun.misc.BASE64Encoder;
import java.awt.Color;
import java.awt.Font;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

/**
 * @desc Office工具类
 * @author -
 * @date -
 */
@Slf4j
public final class OfficeUtils {

    private static final Logger logger = LoggerFactory.getLogger(OfficeUtils.class);
    private static final String FILE_TYPE_IMAGE = "png";
    private static final String FILE_DOT = ".";
    private static final String FILE_CONN = "_";
    private static final String CHART_TYPE_BAR = "bar";
    private static final String CHART_TYPE_PIE = "pie";

    /**
     * 描述
     * @param year 生成报告的年份
     * @param fileRootPath 模板存在的磁盘目录路径
     * @param excelfilepath excelfilepath
     * @param ftlfilepath ftlfilepath
     * @param fileName fileName
     * @param texts word中需要替换的简单字符
     * @param tables word中需要替换的图表数据，二维数据表
     */
    public static void exportWordReport(int year,String fileRootPath,String excelfilepath,String ftlfilepath,String fileName,HashMap<String,String> texts,HashMap<String,List<HashMap<String,String>>> tables) throws Exception {
        //有图表模板excel路径才处理图表
        if(null != excelfilepath && !excelfilepath.isEmpty()) {
            //读取excel文档，读取第一页的配置数据
            Workbook wb = loadExcelTemplate(excelfilepath);
            Map<String,ChartConfig> map = readConfig(wb);
            //生成图表
            executeExcel2Chart(wb,map,fileRootPath,texts,tables,year);
        }
        //生成word
        boolean word = FreemarkerTemplateUtil.isSaveWordFileSuccess(texts,fileRootPath,fileName,ftlfilepath);
        logger.info("word save state:" + word);
    }

    /**
     * 描述
     * @param excelfilepath excelfilepath
     * @return Workbook
     */
    private static Workbook loadExcelTemplate(String excelfilepath) throws Exception {
        if(null == excelfilepath) {
            return null;
        }
        try {
            InputStream is = OfficeUtils.class.getResourceAsStream("/templates/doc/" + excelfilepath);
            assert is != null;
            return new HSSFWorkbook(is);
        } catch(FileNotFoundException e) {
            throw new Exception("没有找到模板文件！filepath：" + excelfilepath);
        }
    }

    /**
     * 描述
     * @param wb wb
     * @return Map<String,ChartConfig>
     */
    private static Map<String,ChartConfig> readConfig(Workbook wb) throws Exception {
        HashMap<String,ChartConfig> map = new HashMap<>(16);
        try {
            int n = wb.getNumberOfSheets();
            Sheet sheet = null;
            Row row;
            if(Constants.Number.NUM_ZERO < n) {
                sheet = wb.getSheetAt(0);
                if(null == sheet) {
                    throw new Exception("模板文件没有任何sheet页！");
                }
            }
            assert sheet != null;
            int lastRowNum = sheet.getLastRowNum();
            for(int i = 1; i <= lastRowNum; i++) {
                row = sheet.getRow(i);
                ChartConfig cc = loadChartConfig(row);
                if(null != cc.id) {
                    map.put(cc.id,cc);
                }
            }
        } catch(Exception e) {
            logger.error("readConfig error:" + e.getMessage(),e);
            throw e;
        }
        return map;
    }

    /**
     * 描述
     * @param row row
     * @return ChartConfig
     */
    private static ChartConfig loadChartConfig(Row row) {
        ChartConfig cc = new ChartConfig();
        if(null != row) {
            for(int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                String value = getCellValue(cell);
                switch(j) {
                    case 0:
                        cc.id = value;
                        break;
                    case 1:
                        cc.setSheetId(value);
                        break;
                    case 2:
                        cc.chartType = value;
                        break;
                    case 3:
                        cc.title = value;
                        break;
                    case 4:
                        cc.setWidth(value);
                        break;
                    case 5:
                        cc.setHeight(value);
                        break;
                    case 6:
                        cc.setTableYear(value);
                        break;
                    case 7:
                        cc.setStartYear(value);
                        break;
                    default:
                        break;
                }
            }
        }
        return cc;
    }

    /**
     * 描述
     * @param cell cell
     * @return String
     */
    private static String getCellValue(Cell cell) {
        Object result = "";
        if(null != cell) {
            switch(cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    result = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    result = cell.getNumericCellValue();
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    result = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    result = cell.getCellFormula();
                    break;
                case Cell.CELL_TYPE_ERROR:
                    result = cell.getErrorCellValue();
                    break;
                default:
                    break;
            }
        }
        return result.toString();
    }

    /**
     * 描述
     * @param wb wb
     * @param configMap configMap
     * @param filePath filePath
     * @param texts texts
     * @param tables tables
     * @param year year
     */
    private static void executeExcel2Chart(Workbook wb,Map<String,ChartConfig> configMap,String filePath,HashMap<String,String> texts,HashMap<String,List<HashMap<String,String>>> tables,int year) throws Exception {
        Set<String> keys = configMap.keySet();
        Iterator<String> iter = keys.iterator();
        String uuid = UUID.randomUUID().toString();
        while(iter.hasNext()) {
            String key = iter.next();
            ChartConfig cc = configMap.get(key);
            List<HashMap<String,String>> list = tables.get(key);
            writeSheetData(wb,list,cc,year);
            String imageName = key + FILE_CONN + uuid + FILE_DOT + FILE_TYPE_IMAGE;
            File image = saveChartAsImage(cc.chart,filePath,imageName,cc.getWidth(),cc.getHeight());
            String str = imageToBase64String(image);
            texts.put(key,str);
            try {
                image.delete();
            } catch(RuntimeException e) {
                log.error(e.getMessage());
            }
        }
    }

    /**
     * 描述
     * @param wb wb
     * @param data data
     * @param cc cc
     * @param year year
     */
    private static void writeSheetData(Workbook wb,List<HashMap<String,String>> data,ChartConfig cc,int year) throws Exception {
        Sheet sheet = wb.getSheetAt(cc.getSheetId());
        if(null == sheet) {
            throw new Exception("模板文件sheet页不存在！sheetId:" + cc.getSheetId());
        }
        if(null == data || data.isEmpty()) {
            throw new Exception("传入的数据表为空！ChartConfig:" + cc);
        }
        //第一行如果是年份，需要处理
        Row firstRow = sheet.getRow(0);
        if(Constants.Number.NUM_ZERO < cc.getTableYear() && Constants.Number.NUM_ZERO < cc.getStartYear() && year != cc.getTableYear()) {
            int inc = year - cc.getTableYear();
            int startYear = cc.getStartYear() + inc;
            //处理该sheet中第一行数据
            if(null != firstRow) {
                for(int i = 1; i < firstRow.getLastCellNum(); i++) {
                    Cell cell = firstRow.getCell(i);
                    String str = startYear + "年";
                    cell.setCellValue(str);
                    startYear++;
                }
            }
        }
        //处理sheet中后面数据
        for(int i = 0; i < data.size(); ) {
            HashMap<String,String> obj = data.get(i);
            i++;
            Row row = sheet.getRow(i);
            if(null != firstRow && null != obj && null != row && !obj.isEmpty()) {
                for(int j = 1; j < firstRow.getLastCellNum(); j++) {
                    Cell cell = firstRow.getCell(j);
                    String key = cell.getStringCellValue();
                    String val = obj.get(key);
                    Cell vc = row.getCell(j);
                    if(null == val || val.isEmpty()) {
                        vc.setCellValue(0);
                    } else {
                        vc.setCellValue(Double.parseDouble(val));
                    }
                }
            }
        }
        //组织jfreeChart的DataSet
        createJfreeChart(sheet,cc);
    }

    /**
     * 描述
     * @param sheet sheet
     * @param cc cc
     */
    private static void createJfreeChart(Sheet sheet,ChartConfig cc) {
        // 创建主题样式
        StandardChartTheme standardChartTheme = new StandardChartTheme("CN");
        // 设置标题字体
        standardChartTheme.setExtraLargeFont(new Font("宋书",Font.BOLD,20));
        // 设置图例的字体
        standardChartTheme.setRegularFont(new Font("宋书",Font.PLAIN,15));
        // 设置轴向的字体
        standardChartTheme.setLargeFont(new Font("宋书",Font.PLAIN,15));
        // 应用主题样式
        ChartFactory.setChartTheme(standardChartTheme);
        if(null == sheet) {
            return;
        }
        if(null == cc) {
            return;
        }
        Row row0 = sheet.getRow(0);
        if(null == row0) {
            return;
        }
        JFreeChart chart;
        if(cc.chartType.startsWith(CHART_TYPE_PIE)) {
            Row row1 = sheet.getRow(1);
            DefaultPieDataset dpd = new DefaultPieDataset();
            if(null != row1) {
                for(int i = 1; i < row0.getLastCellNum(); i++) {
                    String key = row0.getCell(i).getStringCellValue();
                    double value = row1.getCell(i).getNumericCellValue();
                    dpd.setValue(key,value);
                }
            }
            String format = "{0} : {1}({2})";
            if(CHART_TYPE_PIE.equals(cc.chartType)) {
                chart = ChartFactory.createPieChart(cc.title,dpd,true,true,false);
                PiePlot plot = (PiePlot)chart.getPlot();
                plot.setLabelGenerator(new StandardPieSectionLabelGenerator(format));
            } else {
                chart = ChartFactory.createPieChart3D(cc.title,dpd,true,true,false);
                PiePlot3D plot = (PiePlot3D)chart.getPlot();
                plot.setForegroundAlpha(0.8f);
                plot.setCircular(false);
                plot.setDepthFactor(0.05);
                plot.setLabelGenerator(new StandardPieSectionLabelGenerator(format));
            }
        } else {
            DefaultCategoryDataset dcd = new DefaultCategoryDataset();
            int lastRowNum = sheet.getLastRowNum();
            double max = Constants.Number.NUM_ZERO;
            for(int i = 1; i < row0.getLastCellNum(); i++) {
                String key = row0.getCell(i).getStringCellValue();
                for(int j = 1; j <= lastRowNum; j++) {
                    Row r = sheet.getRow(j);
                    double value = r.getCell(i).getNumericCellValue();
                    if(value > max) {
                        max = value;
                    }
                    String label = r.getCell(0).getStringCellValue();
                    dcd.addValue(value,label,key);
                }
            }
            if(CHART_TYPE_BAR.equals(cc.chartType)) {
                chart = ChartFactory.createBarChart(cc.title,"","",dcd,PlotOrientation.VERTICAL,true,true,false);
                setMaxBarWidth(chart,0.07);
            } else {
                chart = ChartFactory.createBarChart(cc.title,"","",dcd,PlotOrientation.HORIZONTAL,true,true,false);
                setMaxBarWidth(chart,0.09);
            }
            setBarChartRenderer(chart);
            setValueAxisRange(chart,max);
        }
        cc.chart = chart;
    }

    /**
     * 描述
     * @param jfreechart jfreechart
     * @param percent percent
     */
    private static void setMaxBarWidth(JFreeChart jfreechart,double percent) {
        CategoryPlot categoryplot = (CategoryPlot)jfreechart.getPlot();
        BarRenderer barrenderer = (BarRenderer)categoryplot.getRenderer();
        //设置柱子宽度
        barrenderer.setMaximumBarWidth(percent);
    }

    /**
     * 描述
     * @param jfreechart jfreechart
     */
    private static void setBarChartRenderer(JFreeChart jfreechart) {
        CategoryPlot categoryplot = (CategoryPlot)jfreechart.getPlot();
        BarRenderer barrenderer = (BarRenderer)categoryplot.getRenderer();
        //显示每个柱的数值
        barrenderer.setBaseItemLabelGenerator(new StandardCategoryItemLabelGenerator());
        barrenderer.setBaseItemLabelsVisible(true);
        //红
        barrenderer.setSeriesPaint(0,new Color(220,20,60));
        //蓝
        barrenderer.setSeriesPaint(1,new Color(65,105,225));
        barrenderer.setBarPainter(new StandardBarPainter());
        barrenderer.setDrawBarOutline(false);
    }

    /**
     * 描述
     * @param chart chart
     * @param max max
     */
    private static void setValueAxisRange(JFreeChart chart,double max) {
        //设置图的高级属性
        CategoryPlot plot = chart.getCategoryPlot();
        //对Y轴做操作
        ValueAxis rAxis = plot.getRangeAxis();
        if(Constants.Number.NUM_ZERO.equals(max)) {
            max = 0.1;
        }
        max *= 1.2;
        rAxis.setRange(0,max);
    }

    /**
     * 描述
     * @param chart chart
     * @param filePath filePath
     * @param fileName fileName
     * @param width width
     * @param height height
     * @return File
     */
    private static File saveChartAsImage(JFreeChart chart,String filePath,String fileName,int width,int height) throws IOException {
        File image = new File(filePath + fileName);
        ChartUtilities.saveChartAsPNG(image,chart,width,height);
        return image;
    }

    /**
     * 描述
     * @param image image
     * @return String
     */
    private static String imageToBase64String(File image) {
        //将图片文件转化为字节数组字符串，并对其进行Base64编码处理
        InputStream in = null;
        byte[] data = null;
        //读取图片字节数组
        try {
            in = Files.newInputStream(image.toPath());
            data = new byte[in.available()];
            in.read(data);
            in.close();
        } catch(IOException e) {
            log.error("发生异常:",e);
        } finally {
            if(null != in) {
                try {
                    in.close();
                } catch(IOException e) {
                    log.error("发生异常:",e);
                }
            }
        }
        //对字节数组Base64编码
        BASE64Encoder encoder = new BASE64Encoder();
        return encoder.encode(data);
        //返回Base64编码过的字节数组字符串
    }
}